package com.aizhixin.lab.statistics.service;

import com.aizhixin.lab.common.core.ApiReturnConstants;
import com.aizhixin.lab.common.dto.SortDTO;
import com.aizhixin.lab.common.utils.PageJdbcUtil;
import com.aizhixin.lab.statistics.domain.*;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

@Slf4j
@Service
@Transactional
public class AdminService {
    @Autowired
    private PageJdbcUtil pageJdbcUtil;

    public CourseDataDomain getData(QueryCourseDomain domain) {
        String sql = "";
        if (domain.getIsPublish() != null) {

            if (domain.getIsPublish() == 1) {
                sql = " AND t.created_by<>4";
            } else if (domain.getIsPublish() == 0) {
                sql = " AND t.created_by=4";
            }
        }
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        if (domain.getBeginDate() != null) {
            sql += " AND c.created_date BETWEEN '" + formatter.format(domain.getBeginDate()) + "' AND '" + formatter.format(domain.getEndDate()) + "'";
        }
        if (domain.getCollegeId() != null) {
            sql += " and tu.COLLEGE_ID=" + domain.getCollegeId();
        }
        CourseDataDomain courseDataDomain = new CourseDataDomain();
        courseDataDomain.setAdminCourseNum(pageJdbcUtil.getCountInt("SELECT COUNT(c.id) FROM c_template c  WHERE c.delete_flag=0 AND c.created_by=4"));
        courseDataDomain.setCourseTotal(pageJdbcUtil.getCountInt("SELECT COUNT(c.id) FROM c_template c LEFT JOIN t_user tu ON tu.id=c.created_by WHERE c.delete_flag=0 AND tu.delete_flag=0 "));
        courseDataDomain.setNewCourseNum(pageJdbcUtil.getCountInt("SELECT COUNT(c.id) FROM c_template c LEFT JOIN t_user tu ON tu.id=c.created_by WHERE c.delete_flag=0 AND c.created_by<>4 AND tu.delete_flag=0 "));
        courseDataDomain.setCiteCourseNum(pageJdbcUtil.getCountInt("SELECT COUNT(DISTINCT c.template_id) FROM c_course c LEFT JOIN t_user tu ON c.created_by=tu.id LEFT JOIN c_template t  ON t.id=c.template_id WHERE c.delete_flag=0 AND c.is_publish=0 AND t.delete_flag=0 AND tu.delete_flag=0"));
        courseDataDomain.setPrepareLessonsTotalNum(pageJdbcUtil.getCountInt("SELECT COUNT(c.id) FROM c_course c LEFT JOIN c_template t ON t.id=c.template_id LEFT JOIN t_user tu ON tu.id=c.created_by WHERE c.delete_flag=0 AND c.is_publish=0 AND t.delete_flag=0 AND tu.delete_flag=0 AND tu.COLLEGE_ID is not NULL" + sql));
        courseDataDomain.setGiveLessonsTotalNum(pageJdbcUtil.getCountInt("SELECT COUNT(c.id) FROM c_course c LEFT JOIN c_template t ON t.id=c.template_id LEFT JOIN t_user tu ON tu.id=c.created_by  WHERE c.delete_flag=0 AND c.is_publish=1 AND t.delete_flag=0 AND tu.delete_flag=0 AND tu.COLLEGE_ID is not NULL" + sql));
        courseDataDomain.setTeacherCourseTotalNum(pageJdbcUtil.getCountInt("SELECT COUNT(DISTINCT c.created_by) FROM c_course c LEFT JOIN c_template t ON t.id=c.template_id LEFT JOIN t_user tu ON tu.id=c.created_by WHERE c.delete_flag=0  AND t.delete_flag=0 AND tu.delete_flag=0 AND tu.COLLEGE_ID is not NULL" + sql));
        return courseDataDomain;
    }

    RowMapper<GiveLessonsDomain> sv = new RowMapper<GiveLessonsDomain>() {

        @Override
        public GiveLessonsDomain mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub
            GiveLessonsDomain domain = new GiveLessonsDomain();
            domain.setCourseName(rs.getString(1));
            domain.setTeacherName(rs.getString(2));
            domain.setCreateName(rs.getString(3));
            domain.setId(rs.getLong(4));
            domain.setCollege(rs.getString(6));
            domain.setPrepareLessonsNum(pageJdbcUtil.getCountInt("SELECT COUNT(*) FROM c_course c LEFT JOIN c_template t ON c.template_id=t.id WHERE t.delete_flag=0 and c.delete_flag=0 AND c.is_publish=0 AND c.template_id=" + domain.getId() + " and c.created_by=" + rs.getLong(5)));
            domain.setGiveLessonsNum(pageJdbcUtil.getCountInt("SELECT COUNT(*) FROM c_course c LEFT JOIN c_template t ON c.template_id=t.id WHERE t.delete_flag=0 and c.delete_flag=0 AND c.is_publish=1 AND c.template_id=" + domain.getId() + " and c.created_by=" + rs.getLong(5)));
            return domain;
        }
    };

    public Map<String, Object> getList(QueryCourseDomain domain) {
        String sql = "SELECT DISTINCT c.`name`,tu.`name` teacher_name,u.`name` create_name ,c.template_id,c.created_by,tc.name college_name FROM c_course c LEFT JOIN t_user tu ON c.created_by=tu.id LEFT JOIN c_template t ON t.id=c.template_id LEFT JOIN t_user u ON t.created_by=u.id LEFT JOIN t_college tc ON u.COLLEGE_ID=tc.ID WHERE t.delete_flag=0 AND c.delete_flag=0 AND t.delete_flag=0 AND u.delete_flag=0 AND tu.delete_flag=0";
        String count = "SELECT count(DISTINCT c.`name`,tu.`name`,u.`name`,c.template_id,c.created_by) FROM c_course c LEFT JOIN t_user tu ON c.created_by=tu.id LEFT JOIN c_template t ON t.id=c.template_id LEFT JOIN t_user u ON t.created_by=u.id WHERE t.delete_flag=0 AND c.delete_flag=0 AND t.delete_flag=0 AND u.delete_flag=0 AND tu.delete_flag=0 ";
        String a = "";
        String b = "";
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        if (domain.getBeginDate() != null) {
            a += " AND c.created_date BETWEEN '" + formatter.format(domain.getBeginDate()) + "' AND '" + formatter.format(domain.getEndDate()) + "'";
            b += " AND c.created_date BETWEEN '" + formatter.format(domain.getBeginDate()) + "' AND '" + formatter.format(domain.getEndDate()) + "'";
        }
        if (domain.getTeacherId() != null) {
            a += " AND c.created_by=" + domain.getTeacherId();
            b += "AND c.created_by=" + domain.getTeacherId();
        }
        if (domain.getCourseId() != null) {
            a += " AND c.template_id=" + domain.getCourseId();
            b += " AND c.template_id=" + domain.getCourseId();
        }
        if (domain.getIsPublish() != null) {
            if (domain.getIsPublish() == 0) {
                a += " AND t.created_by=4";
                b += " AND t.created_by=4";
            } else if (domain.getIsPublish() == 1) {
                a += " AND t.created_by<>4";
                b += " AND t.created_by<>4";
            }
        }
        if (null != domain.getCollegeId() && domain.getCollegeId().intValue() > 0L) {
            a += " and tu.COLLEGE_ID =" + domain.getCollegeId() + "";
            b += " and tu.COLLEGE_ID =" + domain.getCollegeId() + "";
        }
        List<SortDTO> sort = new ArrayList<SortDTO>();
        SortDTO dto = new SortDTO();
        dto = new SortDTO();
        dto.setKey("c.created_date");
        dto.setAsc(false);
        sort.add(dto);
        Map<String, Object> pageInfor = pageJdbcUtil.getPageInfor(domain.getPageSize(), domain.getPageNumber(), sv, sort, sql + a, count + b);

        return pageInfor;
    }

    RowMapper<ProjectGiveLessonsDomain> pv = new RowMapper<ProjectGiveLessonsDomain>() {

        @Override
        public ProjectGiveLessonsDomain mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub
            ProjectGiveLessonsDomain domain = new ProjectGiveLessonsDomain();
            domain.setProjectName(rs.getString(1));
            domain.setTeacherName(rs.getString(2));
            domain.setCreateName(rs.getString(3));
//            domain.setId(rs.getLong(4));
            domain.setCollege(rs.getString(6));
            domain.setPrepareLessonsNum(pageJdbcUtil.getCountInt("SELECT COUNT(*) FROM c_project c LEFT JOIN t_project t ON c.template_id=t.id WHERE t.delete_flag=0 and c.delete_flag=0 AND c.is_publish=0 AND c.template_id='" + rs.getString(4) + "' and c.created_by=" + rs.getLong(5)));
            domain.setGiveLessonsNum(pageJdbcUtil.getCountInt("SELECT COUNT(*) FROM c_project c LEFT JOIN t_project t ON c.template_id=t.id WHERE t.delete_flag=0 and c.delete_flag=0 AND c.is_publish=1 AND c.template_id='" + rs.getString(4) + "' and c.created_by=" + rs.getLong(5)));
            return domain;
        }
    };

    public Map<String, Object> getProjectList(QueryProjectDomain domain) {
        String sql = "SELECT DISTINCT c.project_name,tu.`name`,u.`name`,c.template_id ,c.created_by,tc.name college_name FROM c_project c LEFT JOIN t_user tu ON c.created_by=tu.id LEFT JOIN t_project t ON t.id=c.template_id LEFT JOIN t_user u ON t.created_by=u.id LEFT JOIN t_college tc ON tu.COLLEGE_ID=tc.ID  WHERE t.delete_flag=0 AND c.delete_flag=0 AND t.delete_flag=0 AND u.delete_flag=0 ";
        String count = "SELECT count(DISTINCT c.project_name,tu.`name`,u.`name`,c.template_id ,c.created_by,tc.name) FROM c_project c LEFT JOIN t_user tu ON c.created_by=tu.id LEFT JOIN t_project t ON t.id=c.template_id LEFT JOIN t_user u ON t.created_by=u.id LEFT JOIN t_college tc ON tu.COLLEGE_ID=tc.ID  WHERE t.delete_flag=0 AND c.delete_flag=0 AND t.delete_flag=0 AND u.delete_flag=0 ";
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        if (domain.getBeginDate() != null) {
            sql += " AND c.created_date BETWEEN '" + formatter.format(domain.getBeginDate()) + "' AND '" + formatter.format(domain.getEndDate()) + "'";
            count += " AND c.created_date BETWEEN '" + formatter.format(domain.getBeginDate()) + "' AND '" + formatter.format(domain.getEndDate()) + "'";
        }
        if (domain.getTeacherId() != null) {
            sql += " AND c.created_by=" + domain.getTeacherId();
            count += "AND c.created_by=" + domain.getTeacherId();
        }
        if (domain.getProjectId() != null) {
            sql += " AND c.template_id='" + domain.getProjectId()+"' ";
            count += " AND c.template_id='" + domain.getProjectId()+"' ";
        }
        if (domain.getIsPublish() != null) {
            if (domain.getIsPublish() == 0) {
                sql += " AND t.created_by=4";
                count += " AND t.created_by=4";
            } else if (domain.getIsPublish() == 1) {
                sql += " AND t.created_by<>4";
                count += " AND t.created_by<>4";
            }
        }
        if (null != domain.getCollegeId() && domain.getCollegeId().intValue() > 0L) {
            sql += " and tu.COLLEGE_ID =" + domain.getCollegeId() + "";
            count += " and tu.COLLEGE_ID =" + domain.getCollegeId() + "";
        }
        List<SortDTO> sort = new ArrayList<SortDTO>();
        SortDTO dto = new SortDTO();
        dto = new SortDTO();
        dto.setKey("c.created_date");
        dto.setAsc(false);
        sort.add(dto);
        Map<String, Object> pageInfor = pageJdbcUtil.getPageInfor(domain.getPageSize(), domain.getPageNumber(), pv, sort, sql, count);
        return pageInfor;
    }
    public ProjectDataDomain getProjectData(QueryProjectDomain domain) {
        String sql = "";
        if (domain.getIsPublish() != null) {

            if (domain.getIsPublish() == 1) {
                sql = " AND t.created_by<>4";
            } else if (domain.getIsPublish() == 0) {
                sql = " AND t.created_by=4";
            }
        }
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        if (domain.getBeginDate() != null) {
            sql += " AND c.created_date BETWEEN '" + formatter.format(domain.getBeginDate()) + "' AND '" + formatter.format(domain.getEndDate()) + "'";
        }
        if (domain.getCollegeId() != null) {
            sql += " and tu.COLLEGE_ID=" + domain.getCollegeId();
        }
        ProjectDataDomain projectDataDomain = new ProjectDataDomain();
        projectDataDomain.setAdminProjectNum(pageJdbcUtil.getCountInt("SELECT COUNT(c.id) FROM t_project c  WHERE c.delete_flag=0 AND c.created_by=4"));
        projectDataDomain.setProjectTotal(pageJdbcUtil.getCountInt("SELECT COUNT(c.id) FROM t_project c LEFT JOIN t_user tu ON tu.id=c.created_by WHERE c.delete_flag=0 AND tu.delete_flag=0 "));
        projectDataDomain.setNewProjectNum(pageJdbcUtil.getCountInt("SELECT COUNT(c.id) FROM t_project c LEFT JOIN t_user tu ON tu.id=c.created_by WHERE c.delete_flag=0 AND c.created_by<>4 AND tu.delete_flag=0 "));
        projectDataDomain.setCiteProjectNum(pageJdbcUtil.getCountInt("SELECT COUNT(DISTINCT c.template_id) FROM c_course c LEFT JOIN t_user tu ON c.created_by=tu.id LEFT JOIN c_template t  ON t.id=c.template_id WHERE c.delete_flag=0 AND c.is_publish=0 AND t.delete_flag=0 AND tu.delete_flag=0"));
        projectDataDomain.setPrepareLessonsTotalNum(pageJdbcUtil.getCountInt("SELECT COUNT(c.id) FROM c_project c LEFT JOIN t_project t ON t.id=c.template_id LEFT JOIN t_user tu ON tu.id=c.created_by WHERE c.delete_flag=0 AND c.is_publish=0 AND t.delete_flag=0 AND tu.delete_flag=0 AND tu.COLLEGE_ID is not NULL" + sql));
        projectDataDomain.setGiveLessonsTotalNum(pageJdbcUtil.getCountInt("SELECT COUNT(c.id) FROM c_project c LEFT JOIN t_project t ON t.id=c.template_id LEFT JOIN t_user tu ON tu.id=c.created_by  WHERE c.delete_flag=0 AND c.is_publish=1 AND t.delete_flag=0 AND tu.delete_flag=0 AND tu.COLLEGE_ID is not NULL" + sql));
        projectDataDomain.setTeacherProjectTotalNum(pageJdbcUtil.getCountInt("SELECT COUNT(DISTINCT c.created_by) FROM c_project c LEFT JOIN t_project t ON t.id=c.template_id LEFT JOIN t_user tu ON tu.id=c.created_by WHERE c.delete_flag=0  AND t.delete_flag=0 AND tu.delete_flag=0 AND tu.COLLEGE_ID is not NULL" + sql));
        return projectDataDomain;
    }
    RowMapper<TemplateUseDomain> courseRm = new RowMapper<TemplateUseDomain>() {

        @Override
        public TemplateUseDomain mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub
            TemplateUseDomain domain = new TemplateUseDomain();
            domain.setId(rs.getLong(1));
            domain.setCourseName(rs.getString(2));
            domain.setCreateName(rs.getString(3));
            domain.setCollege(rs.getString(4));
            domain.setPrepareLessonsNum(pageJdbcUtil.getCountInt("SELECT COUNT(c.id) FROM c_course c LEFT JOIN c_template t ON c.template_id=t.id WHERE t.delete_flag=0 and c.delete_flag=0 AND c.is_publish=0 AND c.template_id=" + domain.getId()));
            domain.setGiveLessonsNum(pageJdbcUtil.getCountInt("SELECT COUNT(c.id) FROM c_course c LEFT JOIN c_template t ON c.template_id=t.id WHERE t.delete_flag=0 and c.delete_flag=0 AND c.is_publish=1 AND c.template_id=" + domain.getId()));
            Set<String> set=new HashSet<>(pageJdbcUtil.getInfo("SELECT tu.name FROM c_course c LEFT JOIN t_user tu ON c.created_by=tu.id WHERE c.template_id="+domain.getId()+" AND c.delete_flag=0 AND tu.delete_flag=0",sRm)) ;
            domain.setTeacherNameList(set);
            return domain;
        }
    };
    RowMapper<String> sRm = new RowMapper<String>() {

        @Override
        public String mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub
             return rs.getString(1);
        }
    };
    public Map<String, Object> getCourse(QueryCourseDomain domain){
        String sql="SELECT c.id,c.`name`,tu.`name`,tc.`NAME` FROM c_template c LEFT JOIN t_user tu on c.created_by=tu.id LEFT JOIN t_college tc ON tu.COLLEGE_ID=tc.ID WHERE c.delete_flag=0";
        String countSql="SELECT count(*) FROM c_template c LEFT JOIN t_user tu on c.created_by=tu.id LEFT JOIN t_college tc ON tu.COLLEGE_ID=tc.ID WHERE c.delete_flag=0";
        List<SortDTO> sort = new ArrayList<SortDTO>();
        SortDTO dto = new SortDTO();
        dto = new SortDTO();
        dto.setKey("c.created_date");
        dto.setAsc(false);
        sort.add(dto);
        Map<String, Object> pageInfor = pageJdbcUtil.getPageInfor(domain.getPageSize(), domain.getPageNumber(), courseRm, sort, sql, countSql);
        return pageInfor;
    }
}
